--    Author    : XUEZHOUYI
--    Name      : ODS.HBASE_DEPB.HQL
--    Functions : 
--    Purpose   : 
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-03-10  XUEZHOUYI       记录有可能重复，为了避免重复记录有同样的MD5需要衍生一列序号来区分
--

INSERT OVERWRITE TABLE ODS.HBASE_DEPB
SELECT
     F_CREATEMD5(TMP.RN,TMP.DATA_DATE,TMP.AREA_NO_ID,TMP.DATA_SRC_ORG,TMP.DATA_ORG_CODE,TMP.CUSTOMER_TYPE,TMP.ACCOUNT_CODE,TMP.DEP_AGREEMENT_CODE,TMP.PRODUCT_CATEGORY,TMP.DEP_AGREEMENT_SDATE,TMP.DEP_AGREEMENT_EDATE,TMP.TERM,TMP.DEP_CUR_TYPE,TMP.DEP_BALANCE,TMP.RATE_FIXED_FLOAT,TMP.RATE_LEVEL) AS UUID
    ,TMP.DATA_DATE          
    ,TMP.AREA_NO_ID          
    ,TMP.DATA_SRC_ORG       
    ,TMP.DATA_ORG_CODE      
    ,TMP.CUSTOMER_TYPE      
    ,TMP.ACCOUNT_CODE       
    ,TMP.DEP_AGREEMENT_CODE 
    ,TMP.PRODUCT_CATEGORY   
    ,TMP.DEP_AGREEMENT_SDATE
    ,TMP.DEP_AGREEMENT_EDATE
    ,TMP.TERM
    ,TMP.DEP_CUR_TYPE       
    ,TMP.DEP_BALANCE        
    ,TMP.RATE_FIXED_FLOAT   
    ,TMP.RATE_LEVEL         
FROM(
    SELECT
         ROW_NUMBER() OVER(PARTITION BY DEP.DATA_DATE,ORG.DATA_ORG_AREA,DEP.DATA_SRC_ORG,DEP.DATA_ORG_CODE,DEP.CUSTOMER_TYPE,DEP.ACCOUNT_CODE,DEP.DEP_AGREEMENT_CODE,DEP.PRODUCT_CATEGORY,DEP.DEP_AGREEMENT_SDATE,DEP.DEP_AGREEMENT_EDATE,DEP.DEP_CUR_TYPE,DEP.DEP_BALANCE,DEP.RATE_FIXED_FLOAT,DEP.RATE_LEVEL) AS RN
        ,DEP.DATA_DATE          
        ,COALESCE(ORG.DATA_ORG_AREA,'999999') AS AREA_NO_ID
        ,DEP.DATA_SRC_ORG       
        ,DEP.DATA_ORG_CODE      
        ,DEP.CUSTOMER_TYPE      
        ,DEP.ACCOUNT_CODE       
        ,DEP.DEP_AGREEMENT_CODE 
        ,DEP.PRODUCT_CATEGORY   
        ,DEP.DEP_AGREEMENT_SDATE
        ,DEP.DEP_AGREEMENT_EDATE
        ,CEIL(MONTHS_BETWEEN(DEP.DEP_AGREEMENT_EDATE,DEP.DEP_AGREEMENT_SDATE)) AS TERM
        ,DEP.DEP_CUR_TYPE       
        ,DEP.DEP_BALANCE        
        ,DEP.RATE_FIXED_FLOAT   
        ,DEP.RATE_LEVEL         
    FROM ODS.DEPB DEP LEFT JOIN DMCODE.T_ORG_MANAGE ORG ON DEP.DATA_ORG_CODE = ORG.DATA_ORG_ID
    WHERE DEP.DATA_DATE IN('#V_DATA_DATE#','#V_LAST_1M_END_DATE#','#V_LAST_2M_END_DATE#')
)TMP
;
